USE [TOM_MS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AO_SUCKHOE](
	[SUCKHOE_ID] [int] NULL,
	[CHOAN_ID] [int] NULL,
	[VALUE] [nvarchar](50) NULL,
	[CREATED_DATE] [date] NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[HOACHAT](
	[HOACHAT_ID] [int] IDENTITY(1,1) NOT NULL,
	[HOACHAT_NAME] [nvarchar](50) NULL,
	[NOTE] [nvarchar](500) NULL,
	[UNIT_NAME] [nvarchar](50) NULL,
 CONSTRAINT [PK_HOACHAT] PRIMARY KEY CLUSTERED 
(
	[HOACHAT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

USE [TOM_MS]
GO

CREATE TABLE [dbo].[HOACHAT_HIS](
	[HOACHAT_HIS_ID] [int] IDENTITY(1,1) NOT NULL,
	[CHOAN_ID] [int] NULL,
	[HOACHAT_ID] [int] NULL,
	[VALUE] [float] NULL,
 CONSTRAINT [PK_HOACHAT_HIS] PRIMARY KEY CLUSTERED 
(
	[HOACHAT_HIS_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[getHoaChatReport]
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@aoSeasonId int,
	@toDate date
AS
	/* SET NOCOUNT ON */
	
	select a2.hoachatName,a2.unitName,a2.totalValue, a1.createdDate,a1.tomAge  from
	(select log1.choan_id , age as tomAge, created_date as createdDate from choan_log log1 where log1.created_date< @toDate and log1.ao_season_id =@aoSeasonId) a1
	join
	(select  hc.hoachat_name as hoaChatName, hc.unit_name as unitName,a1.totalValue,a1.choan_id from
	(select hcHis.choan_id,sum(value) as totalValue,hcHis.HOACHAT_ID from hoachat_his  hcHis group by choan_id,hcHis.HOACHAT_ID) a1
	join hoachat hc on (a1.hoachat_id = hc.hoachat_id)) a2
	on (a1.choan_id = a2.choan_id)
	
	RETURN
	
	
exec getHoaChatReport 3,'2012-11-11'
GO


